﻿/******************************************************************************
'Description: DataTable导出至Excel工具类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2018.12.25
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Net;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.XSSF.UserModel;
using System.Drawing;

namespace ReportBusiness
{
    public class ExportHelper
    {
        /// <summary>
        /// DataTable导出至Excel
        /// </summary>
        /// <param name="SourceTable">源数据</param>
        /// <param name="title">标题</param>
        /// <param name="isSetColumnWidth">是否设置列为固定宽度</param>
        public static void DataTableExportToExcel(DataTable SourceTable, string title, bool isSetColumnWidth = false)
        {
            if (SourceTable.Rows.Count == 0)
            {
                var res = HttpContext.Current.Response;
                res.StatusCode = 404;
                res.StatusDescription = "File not found";
                return;
            }

            //PAGE_MAX_ROWS => 每个Sheet页最多能够显示的行数。Office 2003最多可显示65536行
            const int PAGE_MAX_ROWS = 65536;

            //coloumsCount => Excel列数
            int coloumsCount = SourceTable.Columns.Count;

            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            #region 单元格样式设置
            ICellStyle style = workbook.CreateCellStyle();
            style.WrapText = true;// 自动换行
            style.VerticalAlignment = VerticalAlignment.Center;// 垂直居中

            //水平对齐居中
            style.Alignment = HorizontalAlignment.Center;

            //设置字体
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            style.SetFont(font);

            //设置边框
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            #endregion

            #region 表头样式设置
            ICellStyle headerCellStyle = workbook.CreateCellStyle();

            //居中显示
            headerCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            headerCellStyle.BorderTop = BorderStyle.Thin;
            headerCellStyle.BorderBottom = BorderStyle.Thin;
            headerCellStyle.BorderLeft = BorderStyle.Thin;
            headerCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            headerCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            headerCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont headerFont = workbook.CreateFont();
            headerFont.Boldweight = short.MaxValue;
            headerFont.FontName = "微软雅黑";
            headerCellStyle.SetFont(headerFont);
            #endregion

            #region 标题样式设置
            ICellStyle titleCellStyle = workbook.CreateCellStyle();

            //居中显示
            titleCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            titleCellStyle.BorderTop = BorderStyle.Thin;
            titleCellStyle.BorderBottom = BorderStyle.Thin;
            titleCellStyle.BorderLeft = BorderStyle.Thin;
            titleCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            titleCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            titleCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont titleFont = workbook.CreateFont();
            titleFont.Boldweight = short.MaxValue;
            titleFont.FontName = "微软雅黑";

            titleCellStyle.SetFont(titleFont);
            #endregion

            int sheetCount = 1;

            //每个sheet页达到最大行数限制时，自动创建新的sheet页
            for (int i = 0; i < SourceTable.Rows.Count; i += (PAGE_MAX_ROWS - 2))
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet" + sheetCount.ToString());

                //创建第1行：标题行
                HSSFRow titleRow = (HSSFRow)sheet.CreateRow(0);

                //为正确显示合并居中行的边框，第一行除第一列之外全部插入空值
                for (int j = 0; j < SourceTable.Columns.Count; j++)
                {
                    if (j == 0)
                    {
                        titleRow.CreateCell(j).SetCellValue(title);
                    }
                    else
                    {
                        titleRow.CreateCell(j).SetCellValue("");
                    }
                    titleRow.Cells[j].CellStyle = titleCellStyle;
                }


                //合并第一行单元格作为Excel标题
                //CellRangeAddress四个参数为：起始行位置，结束行位置，起始列位置，结束列位置（位置由0开始算）
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, coloumsCount - 1));

                //创建第2行：表头行
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);

                //表头行插值并设置样式 
                foreach (DataColumn column in SourceTable.Columns)
                {
                    if (column.Caption.ToString() != column.ColumnName.ToString())
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                    }
                    else
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    headerRow.Cells[column.Ordinal].CellStyle = headerCellStyle;
                }

                //从第3行开始循环插入实际数据
                int rowIndex = 2;

                for (int k = 0; (k < PAGE_MAX_ROWS - 2) && ((k + i) < SourceTable.Rows.Count); k++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        if (column.ColumnName.ToString().IndexOf("_b") > -1)
                        {
                            string value = SourceTable.Rows[i + k][column].ToString();
                            if (value.Contains("<img src=\"data:image/png;base64,"))
                            {
                                int start = value.IndexOf("<img src=\"data:image/png;base64,");
                                int length = "<img src=\"data:image/png;base64,".Length;
                                int end = value.IndexOf("\">");
                                string base64String = value.Substring(start + length, end - start - length);
                                string otherTxtValue = value.Replace(string.Format("<img src=\"data:image/png;base64,{0}\">", base64String), "");

                                dataRow.CreateCell(column.Ordinal).SetCellValue(otherTxtValue);
                                dataRow.Cells[column.Ordinal].CellStyle = style;

                                int _colIndex = dataRow.Cells[column.Ordinal].ColumnIndex;
                                int _rowIndex = dataRow.Cells[column.Ordinal].RowIndex;

                                byte[] data = Convert.FromBase64String(base64String);
                                int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.PNG);

                                ICreationHelper helper = workbook.GetCreationHelper();
                                IDrawing drawing = sheet.CreateDrawingPatriarch();
                                IClientAnchor anchor = helper.CreateClientAnchor();
                                anchor.Dx1 = 0;
                                anchor.Dy1 = 0;
                                anchor.Dx2 = 0;
                                anchor.Dy2 = 0;
                                anchor.Col1 = _colIndex;
                                anchor.Row1 = _rowIndex;
                                anchor.Col2 = _colIndex + 1;
                                anchor.Row2 = _rowIndex + 1;
                                IPicture picture = drawing.CreatePicture(anchor, picInd);
                            }
                            else if (value.Contains("<img src='data:image/png;base64,"))
                            {
                                int start = value.IndexOf("<img src='data:image/png;base64,");
                                int length = "<img src='data:image/png;base64,".Length;
                                int end = value.IndexOf("'>");
                                string base64String = value.Substring(start + length, end - start - length);
                                string otherTxtValue = value.Replace(string.Format("<img src='data:image/png;base64,{0}'>", base64String), "");

                                dataRow.CreateCell(column.Ordinal).SetCellValue(otherTxtValue);
                                dataRow.Cells[column.Ordinal].CellStyle = style;

                                int _colIndex = dataRow.Cells[column.Ordinal].ColumnIndex;
                                int _rowIndex = dataRow.Cells[column.Ordinal].RowIndex;

                                byte[] data = Convert.FromBase64String(base64String);
                                int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.PNG);

                                ICreationHelper helper = workbook.GetCreationHelper();
                                IDrawing drawing = sheet.CreateDrawingPatriarch();
                                IClientAnchor anchor = helper.CreateClientAnchor();
                                anchor.Dx1 = 0;
                                anchor.Dy1 = 0;
                                anchor.Dx2 = 0;
                                anchor.Dy2 = 0;
                                anchor.Col1 = _colIndex;
                                anchor.Row1 = _rowIndex;
                                anchor.Col2 = _colIndex + 1;
                                anchor.Row2 = _rowIndex + 1;
                                IPicture picture = drawing.CreatePicture(anchor, picInd);
                            }
                            else
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(value);
                                dataRow.Cells[column.Ordinal].CellStyle = style;
                            }
                        }
                        else
                        {
                            if (SourceTable.Rows[i + k][column].ToString().Contains(" 0:00:00"))
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i + k][column].ToString().Replace(" 0:00:00", ""));
                            }
                            else
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i + k][column].ToString());
                            }

                            dataRow.Cells[column.Ordinal].CellStyle = style;
                        }
                    }
                    rowIndex++;
                    dataRow = null;
                }

                for (int colIndex = 0; colIndex < SourceTable.Columns.Count; colIndex++)
                {
                    //不设置固定宽度，宽度自适应
                    if (isSetColumnWidth == false)
                    {
                        sheet.AutoSizeColumn(colIndex);
                    }
                    else
                    {
                        sheet.SetColumnWidth(colIndex, 30 * 256);
                    }
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet = null;
                titleRow = null;
                headerRow = null;
                sheetCount++;
            }

            workbook = null;

            var Response = HttpContext.Current.Response;
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", HttpContext.Current.Server.UrlEncode(title), DateTime.Now.ToString("yyyy-MM-dd-HH:mm:ss")));
            Response.AddHeader("Set-Cookie", "fileDownload=true;path=/");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/ms-excel;charset=UTF-8";
            Response.BinaryWrite(ms.ToArray());

            Response.Flush();
            Response.End();
            Response.Close();

            ms.Close();
            ms.Dispose();
        }

        public static void ExportExcel(DataTable SourceTable, string title, bool isSetColumnWidth = false)
        {
            if (SourceTable.Rows.Count == 0)
            {
                var res = HttpContext.Current.Response;
                res.StatusCode = 404;
                res.StatusDescription = "File not found";
                return;
            }

            //PAGE_MAX_ROWS => 每个Sheet页最多能够显示的行数。Office 2003最多可显示65536行
            const int PAGE_MAX_ROWS = 65536;

            //coloumsCount => Excel列数
            int coloumsCount = SourceTable.Columns.Count;

            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            #region 单元格样式设置
            ICellStyle style = workbook.CreateCellStyle();
            style.WrapText = true;// 自动换行
            style.VerticalAlignment = VerticalAlignment.Center;// 垂直居中

            //水平对齐居中
            style.Alignment = HorizontalAlignment.Center;

            //设置字体
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            style.SetFont(font);

            //设置边框
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            #endregion

            #region 表头样式设置
            ICellStyle headerCellStyle = workbook.CreateCellStyle();

            //居中显示
            headerCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            headerCellStyle.BorderTop = BorderStyle.Thin;
            headerCellStyle.BorderBottom = BorderStyle.Thin;
            headerCellStyle.BorderLeft = BorderStyle.Thin;
            headerCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            headerCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            headerCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont headerFont = workbook.CreateFont();
            headerFont.Boldweight = short.MaxValue;
            headerFont.FontName = "微软雅黑";
            headerCellStyle.SetFont(headerFont);
            #endregion

            #region 标题样式设置
            ICellStyle titleCellStyle = workbook.CreateCellStyle();

            //居中显示
            titleCellStyle.Alignment = HorizontalAlignment.Center;

            //设置边框
            titleCellStyle.BorderTop = BorderStyle.Thin;
            titleCellStyle.BorderBottom = BorderStyle.Thin;
            titleCellStyle.BorderLeft = BorderStyle.Thin;
            titleCellStyle.BorderRight = BorderStyle.Thin;

            //设置背景色
            titleCellStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            titleCellStyle.FillPattern = FillPattern.SolidForeground;

            //加粗
            IFont titleFont = workbook.CreateFont();
            titleFont.Boldweight = short.MaxValue;
            titleFont.FontName = "微软雅黑";

            titleCellStyle.SetFont(titleFont);
            #endregion

            int sheetCount = 1;

            //每个sheet页达到最大行数限制时，自动创建新的sheet页
            for (int i = 0; i < SourceTable.Rows.Count; i += (PAGE_MAX_ROWS - 2))
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet" + sheetCount.ToString());
                //创建第1行：表头行
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

                //表头行插值并设置样式 
                foreach (DataColumn column in SourceTable.Columns)
                {
                    if (column.Caption.ToString() != column.ColumnName.ToString())
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
                    }
                    else
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }
                    headerRow.Cells[column.Ordinal].CellStyle = headerCellStyle;
                }

                //从第2行开始循环插入实际数据
                int rowIndex = 1;

                for (int k = 0; (k < PAGE_MAX_ROWS - 1) && ((k + i) < SourceTable.Rows.Count); k++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        if (column.ColumnName.ToString().IndexOf("_b") > -1)
                        {
                            string value = SourceTable.Rows[i + k][column].ToString();
                            if (value.Contains("<img src=\"data:image/png;base64,"))
                            {
                                int start = value.IndexOf("<img src=\"data:image/png;base64,");
                                int length = "<img src=\"data:image/png;base64,".Length;
                                int end = value.IndexOf("\">");
                                string base64String = value.Substring(start + length, end - start - length);
                                string otherTxtValue = value.Replace(string.Format("<img src=\"data:image/png;base64,{0}\">", base64String), "");

                                dataRow.CreateCell(column.Ordinal).SetCellValue(otherTxtValue);
                                dataRow.Cells[column.Ordinal].CellStyle = style;

                                int _colIndex = dataRow.Cells[column.Ordinal].ColumnIndex;
                                int _rowIndex = dataRow.Cells[column.Ordinal].RowIndex;

                                byte[] data = Convert.FromBase64String(base64String);
                                int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.PNG);

                                ICreationHelper helper = workbook.GetCreationHelper();
                                IDrawing drawing = sheet.CreateDrawingPatriarch();
                                IClientAnchor anchor = helper.CreateClientAnchor();
                                anchor.Dx1 = 0;
                                anchor.Dy1 = 0;
                                anchor.Dx2 = 0;
                                anchor.Dy2 = 0;
                                anchor.Col1 = _colIndex;
                                anchor.Row1 = _rowIndex;
                                anchor.Col2 = _colIndex + 1;
                                anchor.Row2 = _rowIndex + 1;
                                IPicture picture = drawing.CreatePicture(anchor, picInd);
                            }
                            else if (value.Contains("<img src='data:image/png;base64,"))
                            {
                                int start = value.IndexOf("<img src='data:image/png;base64,");
                                int length = "<img src='data:image/png;base64,".Length;
                                int end = value.IndexOf("'>");
                                string base64String = value.Substring(start + length, end - start - length);
                                string otherTxtValue = value.Replace(string.Format("<img src='data:image/png;base64,{0}'>", base64String), "");

                                dataRow.CreateCell(column.Ordinal).SetCellValue(otherTxtValue);
                                dataRow.Cells[column.Ordinal].CellStyle = style;

                                int _colIndex = dataRow.Cells[column.Ordinal].ColumnIndex;
                                int _rowIndex = dataRow.Cells[column.Ordinal].RowIndex;

                                byte[] data = Convert.FromBase64String(base64String);
                                int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.PNG);

                                ICreationHelper helper = workbook.GetCreationHelper();
                                IDrawing drawing = sheet.CreateDrawingPatriarch();
                                IClientAnchor anchor = helper.CreateClientAnchor();
                                anchor.Dx1 = 0;
                                anchor.Dy1 = 0;
                                anchor.Dx2 = 0;
                                anchor.Dy2 = 0;
                                anchor.Col1 = _colIndex;
                                anchor.Row1 = _rowIndex;
                                anchor.Col2 = _colIndex + 1;
                                anchor.Row2 = _rowIndex + 1;
                                IPicture picture = drawing.CreatePicture(anchor, picInd);
                            }
                            else
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(value);
                                dataRow.Cells[column.Ordinal].CellStyle = style;
                            }
                        }
                        else
                        {
                            if (SourceTable.Rows[i + k][column].ToString().Contains(" 0:00:00"))
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i + k][column].ToString().Replace(" 0:00:00", ""));
                            }
                            else
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(SourceTable.Rows[i + k][column].ToString());
                            }

                            dataRow.Cells[column.Ordinal].CellStyle = style;
                        }
                    }
                    rowIndex++;
                    dataRow = null;
                }

                for (int colIndex = 0; colIndex < SourceTable.Columns.Count; colIndex++)
                {
                    //不设置固定宽度，宽度自适应
                    if (isSetColumnWidth == false)
                    {
                        sheet.AutoSizeColumn(colIndex);
                    }
                    else
                    {
                        sheet.SetColumnWidth(colIndex, 30 * 256);
                    }
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet = null;
                headerRow = null;
                sheetCount++;
            }

            workbook = null;

            var Response = HttpContext.Current.Response;
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", HttpContext.Current.Server.UrlEncode(title), DateTime.Now.ToString("yyyy-MM-dd-HH:mm:ss")));
            Response.AddHeader("Set-Cookie", "fileDownload=true;path=/");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/ms-excel;charset=UTF-8";
            Response.BinaryWrite(ms.ToArray());

            Response.Flush();
            Response.End();
            Response.Close();

            ms.Close();
            ms.Dispose();
        }
    }
}
